﻿using DHQGHN.DAL.Base;
using DHQGHN.DAL.Common;
using DHQGHN.DAL.Models.DHQG;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DHQGHN.DAL.DAO.DHQG
{
    public class ChiTieuDAO : SqlDAO
    {
        public ChiTieuDAO(string connectionString, System.Type type = null) :
            base(connectionString, type) { ;}

        public const string PROCEDURE_GET_LIST_PAGING = "spChiTieu_GetListPaging";
        public const string PROCEDURE_GET_ALL = "spChiTieu_GetAll";
        public const string PROCEDURE_GET_ALL_CHI_TIEU_RIENG = "spChiTieu_GetAll_Chi_Tieu_Rieng";
        public const string PROCEDURE_INSERT_UPDATE = "spChiTieu_InsertUpdate";
        public const string PROCEDURE_DELETE = "spChiTieu_Delete";
        public const string PROCEDURE_GET_BY_ID = "spChiTieu_GetById";
        public const string PROCEDURE_GET_BY_NAMCHITIEU = "spChiTieu_GetByNamKeHoach";
        public const string PROCEDURE_GET_BY_NAMCHITIEU_DONVI = "spChiTieu_GetByNamKeHoach_DONVI";
        public const string PROCEDURE_GET_BY_NAMCHITIEU_DONVI_GIUANAM = "spChiTieu_GetByNamKeHoach_DONVI_GIUANAM";
        
        public const string PROCEDURE_GET_BY_MAP_CAP_2 = "spChiTieu_GetByNamKeHoach_MaCap2";
        public const string PROCEDURE_GET_CAP1 = "spChiTieu_GETCAP1";
        public const string PROCEDURE_Check_Type = "spChiTieu_Check_Type";
        public const string PROCEDURE_GET_KH_CT = "spChitieu_KHTH_CheckDaChotchua";
        public const string PROCEDURE_GET_KH_CT_DA_CHOT = "spChitieu_KHTH_GetChot";
        public const string PROCEDURE_GET_BY_CAP = "spChitieu_GetByCap";
        public const string PROCEDURE_GET_BY_NGUOI_DUNG_ID = "spChiTieu_GetByNguoiDungId";
                
        public const string PARAM_NAM_KE_HOACH_ID = "@NAM_KE_HOACH_ID";
        public const string PARAM_MOC_KIEM_SOAT_ID = "@MOC_KIEM_SOAT_ID";
        public const string PARAM_NGUOI_DUNG_ID = "@NGUOI_DUNG_ID";
        public const string PARAM_DON_VI_ID = "@DON_VI_ID";
        public const string PARAM_CHI_TIEU_ID = "@CHI_TIEU_ID";
        public const string PARAM_MA = "@MA";
        public const string PARAM_CAP = "@CAP";
        public const string PARAM_MA_CAP1 = "@MA_CAP1";
        public const string PARAM_MA_CAP2 = "@MA_CAP2";
        public const string PARAM_MA_CAP3 = "@MA_CAP3";
        public const string PARAM_TEN_CHI_TIEU = "@TEN_CHI_TIEU";
        public const string PARAM_TEN_CHI_TIEU_CAP1 = "@TEN_CHI_TIEU_CAP1";
        public const string PARAM_TEN_CHI_TIEU_CAP2 = "@TEN_CHI_TIEU_CAP2";
        public const string PARAM_TEN_CHI_TIEU_CAP3 = "@TEN_CHI_TIEU_CAP3";
        public const string PARAM_GHI_CHU = "@GHI_CHU";
        public const string PARAM_DELETED = "@DELETED";
        public const string PARAM_START_DATE = "@START_DATE";
        public const string PARAM_END_DATE = "@END_DATE";
        public const string PARAM_LAST_UPDATED = "@LAST_UPDATED";
        public const string PARAM_TYPE = "@TYPE";
        public const string PARAM_LA_CHI_TIEU_AO = "@LA_CHI_TIEU_AO";
        public const string PARAM_CHO_PHEP_TINH_TONG = "@CHO_PHEP_TINH_TONG";
        public const string PARAM_DON_VI_TINH = "@DON_VI_TINH";        
        public const string PARAM_PAGE_INDEX = "@PAGE_INDEX";
        public const string PARAM_PAGE_SIZE = "@PAGE_SIZE";

        public DataPage<ChiTieu> GetListPaging(ChiTieu model, int pageIndex, int pageSize, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_LIST_PAGING);
                if (model == null)
                {
                    model = new ChiTieu();
                }
                cmd.Parameters.Add(CreateParameter(PARAM_CAP, model.CAP));
                cmd.Parameters.Add(CreateParameter(PARAM_MA, model.MA));
                cmd.Parameters.Add(CreateParameter(PARAM_TEN_CHI_TIEU, model.TEN_CHI_TIEU));
                cmd.Parameters.Add(CreateParameter(PARAM_DELETED, 2));
                cmd.Parameters.Add(CreateParameter(PARAM_PAGE_INDEX, pageIndex));
                cmd.Parameters.Add(CreateParameter(PARAM_PAGE_SIZE, pageSize));
                cmd.Parameters.Add(CreateParameter(PARAM_TYPE, model.TYPE));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, model.DON_VI_ID));
                
                return GetDataPage<ChiTieu>(cmd, pageIndex, pageSize, mapDictionary);
            }
            catch
            {
                throw;
            }
        }
        public ChitieuNew GetChitieubyMacap1(int macap1)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_Check_Type);
                cmd.Parameters.Add(CreateParameter(PARAM_MA_CAP1, macap1));
                return GetSingle<ChitieuNew>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public bool Save(ChiTieu model)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_INSERT_UPDATE);
                cmd.Parameters.Add(CreateParameter(PARAM_CHI_TIEU_ID, model.CHI_TIEU_ID));
                cmd.Parameters.Add(CreateParameter(PARAM_CAP, model.CAP));
                cmd.Parameters.Add(CreateParameter(PARAM_MA, model.MA));
                cmd.Parameters.Add(CreateParameter(PARAM_MA_CAP1, model.MA_CAP1));
                cmd.Parameters.Add(CreateParameter(PARAM_MA_CAP2, model.MA_CAP2));
                cmd.Parameters.Add(CreateParameter(PARAM_MA_CAP3, model.MA_CAP3));
                cmd.Parameters.Add(CreateParameter(PARAM_TEN_CHI_TIEU, model.TEN_CHI_TIEU));
                cmd.Parameters.Add(CreateParameter(PARAM_TEN_CHI_TIEU_CAP1, model.TEN_CHI_TIEU_CAP1));
                cmd.Parameters.Add(CreateParameter(PARAM_TEN_CHI_TIEU_CAP2, model.TEN_CHI_TIEU_CAP2));
                cmd.Parameters.Add(CreateParameter(PARAM_TEN_CHI_TIEU_CAP3, model.TEN_CHI_TIEU_CAP3));
                cmd.Parameters.Add(CreateParameter(PARAM_TYPE, model.TYPE));
                cmd.Parameters.Add(CreateParameter(PARAM_LAST_UPDATED, DateTime.Today));
                //cmd.Parameters.Add(CreateParameter(PARAM_START_DATE, model.StartDate));
                //cmd.Parameters.Add(CreateParameter(PARAM_END_DATE, model.EndDate));
                cmd.Parameters.Add(CreateParameter(PARAM_GHI_CHU, model.GHI_CHU));
                cmd.Parameters.Add(CreateParameter(PARAM_LA_CHI_TIEU_AO , model.LA_CHI_TIEU_AO));
                cmd.Parameters.Add(CreateParameter(PARAM_CHO_PHEP_TINH_TONG, model.CHO_PHEP_TINH_TONG));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, model.DON_VI_ID));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_TINH, model.DON_VI_TINH));

                int result = ExecuteNonQuery(cmd);

                return result > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }

        public bool Delete(int id)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_DELETE);
                cmd.Parameters.Add(CreateParameter(PARAM_CHI_TIEU_ID, id));

                int result = ExecuteNonQuery(cmd);

                return result > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }
        public List<ChitieuNew> Getalllist()
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_ALL);
                return GetList<ChitieuNew>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<ChitieuNew> Getlistkhthchitieu(int namkh, int mocks, int donvi, int nguoidungid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_KH_CT);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(CreateParameter(PARAM_MOC_KIEM_SOAT_ID, mocks));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donvi));
                cmd.Parameters.Add(CreateParameter(PARAM_NGUOI_DUNG_ID, nguoidungid));
                return GetList<ChitieuNew>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<ChitieuNew> Getlistkhthchitieudachot(int namkh, int mocks, int donvi, int nguoidungid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_KH_CT_DA_CHOT);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namkh));
                cmd.Parameters.Add(CreateParameter(PARAM_MOC_KIEM_SOAT_ID, mocks));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, donvi));
                cmd.Parameters.Add(CreateParameter(PARAM_NGUOI_DUNG_ID, nguoidungid));
                return GetList<ChitieuNew>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<ChitieuNew> Getallchitieurieng(int dvid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_ALL_CHI_TIEU_RIENG);
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, dvid));
                return GetList<ChitieuNew>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<ChitieuNew> GETCAP1(int namchitieuid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_CAP1);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namchitieuid));
                return GetList<ChitieuNew>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<ChitieuNew> Getalllistbynamchitieu( int namchitieuid , int madonvi)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_NAMCHITIEU);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namchitieuid));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID,madonvi));
                return GetList<ChitieuNew>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<ChitieuNew> Getalllistbynamchitieubydonvi(int namchitieuid, int madonvi, int nguoidungid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_NAMCHITIEU_DONVI);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namchitieuid));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, madonvi));
                cmd.Parameters.Add(CreateParameter(PARAM_NGUOI_DUNG_ID, nguoidungid));
                return GetList<ChitieuNew>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<ChitieuNew> GetalllistbynamchitieubydonviGiuanam(int namchitieuid, int madonvi, int nguoidungid)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_NAMCHITIEU_DONVI_GIUANAM);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namchitieuid));
                cmd.Parameters.Add(CreateParameter(PARAM_DON_VI_ID, madonvi));
                cmd.Parameters.Add(CreateParameter(PARAM_NGUOI_DUNG_ID, nguoidungid));
                return GetList<ChitieuNew>(cmd);
            }
            catch
            {
                throw;
            }
        }
        public List<ChitieuNew> Getallchitieubymacap2(int namchitieuid,int macap2)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_MAP_CAP_2);
                cmd.Parameters.Add(CreateParameter(PARAM_NAM_KE_HOACH_ID, namchitieuid));
                cmd.Parameters.Add(CreateParameter(PARAM_MA_CAP2, macap2));
                return GetList<ChitieuNew>(cmd);
            }
            catch
            {
                throw;
            }
        }

        public ChiTieu GetById(int id, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_ID);
                cmd.Parameters.Add(CreateParameter(PARAM_CHI_TIEU_ID, id));

                return GetSingle<ChiTieu>(cmd, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        public List<ChiTieu> GetByMaCap1_Cap(int cap, string maCap1)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spChiTieu_GetByMaCap1_Cap");
                cmd.Parameters.Add(CreateParameter("@MA_CAP1", maCap1));
                cmd.Parameters.Add(CreateParameter("@CAP", cap));

                return GetList<ChiTieu>(cmd);
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        public ChitieuNew GetCap2ByMaCap2_Cap(int cap, string maCap2)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spChiTieu_GetByMaCap2_Cap");
                cmd.Parameters.Add(CreateParameter("@MA_CAP2", maCap2));
                cmd.Parameters.Add(CreateParameter("@CAP", cap));

                return GetSingle<ChitieuNew>(cmd);
            }
            catch (Exception)
            {

                throw;
            }
        }

        public List<ChiTieu> GetByCap(int cap)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_CAP);
                cmd.Parameters.Add(CreateParameter(PARAM_CAP, cap));

                return GetList<ChiTieu>(cmd);
            }
            catch
            {

                throw;
            }
        }

        public long GetMaxMaCap1()
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spChiTieu_GetMax_MaCap1");

                object result = this.ExecuteScalar(cmd);
                long value = result != DBNull.Value ? Convert.ToInt64(result) : 0;

                return value;
            }
            catch
            {                
                throw;
            }
        }

        public long GetMaxMaCap2()
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spChiTieu_GetMax_MaCap2");

                object result = this.ExecuteScalar(cmd);
                long value = result != DBNull.Value ? Convert.ToInt64(result) : 0;

                return value;
            }
            catch
            {
                throw;
            }
        }

        public long GetMaxMaCap3()
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spChiTieu_GetMax_MaCap3");

                object result = this.ExecuteScalar(cmd);
                long value = result != DBNull.Value ? Convert.ToInt64(result) : 0;

                return value;
            }
            catch
            {
                throw;
            }
        }

        public List<ChitieuNew> GetByNguoiDungId(int nguoiDungId, int namKHId)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(PROCEDURE_GET_BY_NGUOI_DUNG_ID);
                cmd.Parameters.Add(CreateParameter("@NGUOI_DUNG_ID", nguoiDungId));
                cmd.Parameters.Add(CreateParameter("@NAM_KE_HOACH_ID", namKHId));

                return GetList<ChitieuNew>(cmd);
            }
            catch 
            {                
                throw;
            }
        }
    }
}
